# Frierson, Dylan, Lab 3
# 1
USE Books
SELECT Title, Name, Phone
FROM Books, Publisher
Where Books.PubID = Publisher.PubID;
# 2
USE Books
SELECT CONCAT(FirstName, ' ', LastName) AS 'Full Name', ShipDate, Ordernum
FROM Orders, Customers
WHERE Orders.Customernum = Customers.Customernum
AND ShipDate IS NULL;
# 3
USE vbauto
SELECT CONCAT(FName, " ", LName) AS 'Full Name', Manufacturer, ModelName
FROM Customer, Vehicle
WHERE Customer.InventoryIDNumber = Vehicle.InventoryID
AND Manufacturer != 'Chevrolet'
ORDER BY MAnufacturer, ModelName;
# 4 
USE Books
SELECT DISTINCT Title, CONCAT(FirstName, ' ', LastName) As 'Full Name' 
FROM Customers, Books, Orders, OrderItems
WHERE Customers.Customernum = Orders.Customernum AND Orders.Ordernum = OrderItems.Ordernum 
AND OrderItems.ISBN = Books.ISBN AND Customers.FirstName = 'Jake' AND Customers.LastName = 'Lucas';
# 5
USE vbvideo
SELECT Title, StudioName, Length
FROM Video, Studio
WHERE Video.StudioID = Studio.StudioID
ORDER BY Length DESC;
# 6
USE Books
SELECT Lname, Books.ISBN, Title
FROM Author, Books, BookAuthor
WHERE Author.AuthorID = BookAuthor.AuthorID AND BookAuthor.ISBN = Books.ISBN 
AND Author.Lname = 'Adams';
# 7
USE employee 
SELECT first_name, birth_date, tool_name, purchase_date, (TO_DAYS(purchase_date)-TO_DAYS(birth_date))/365.25 AS "age_when_purchased"
FROM employee, tools
WHERE employee.payroll_number = tools.fk_payroll_number 
AND purchase_date BETWEEN '1966-01-01' AND '1990-01-01';
# 8
USE Books
SELECT Title, CONCAT(C.Fname, ' ', C.Lname) AS 'Author Name', CONCAT(F.FirstName, ' ', F.LastName) AS 'Customer Name', OrderDate
FROM Books AS A, BookAuthor AS B, Author AS C, OrderItems AS D, Orders AS E, Customers AS F
WHERE F.Customernum = E.Customernum AND E.Ordernum = D.OrderNum AND D.ISBN = A.ISBN 
AND A.ISBN = B.ISBN AND B.AuthorID = C.AuthorId 
AND F.Firstname = 'Becca' AND F.LastName = 'Nelson';
# 9
USE Books
SELECT Customers.Customernum, CONCAT(FirstName, ' ', LastName) AS Customer, Ordernum, OrderDate
FROM Customers LEFT JOIN Orders
ON Customers.Customernum = Orders.Customernum
ORDER BY Customer;
# 10
USE Books
SELECT Title, Ordernum, Quantity
FROM Books LEFT JOIN OrderItems
ON Books.ISBN = OrderItems.ISBN
WHERE Title LIKE '%A%A%'
ORDER BY Ordernum;